package cn.qdu.dao;

import cn.qdu.model.User;
import cn.qdu.dao.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    // 用户注册
    public boolean register(User user) throws SQLException {
        String sql = "INSERT INTO users(user_id, nickname, phone, email, avatar, birth_date, gender, password, created_at) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        System.out.println("执行注册 SQL: " + sql);
        System.out.println("SQL 参数: " +
                "user_id=" + user.getUserId() + ", " +
                "nickname=" + user.getNickname() + ", " +
                "phone=" + user.getPhone() + ", " +
                "email=" + user.getEmail() + ", " +
                "avatar=" + user.getAvatar() + ", " +
                "birth_date=" + user.getBirthDate() + ", " +
                "gender=" + user.getGender() + ", " +
                "password=" + user.getPassword());
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getUserId());
            stmt.setString(2, user.getNickname());
            stmt.setString(3, user.getPhone());
            stmt.setString(4, user.getEmail());
            stmt.setString(5, user.getAvatar()); // 关键修正点
            stmt.setDate(6, user.getBirthDate());
            stmt.setString(7, user.getGender());
            stmt.setString(8, user.getPassword());

            int affectedRows = stmt.executeUpdate();
            System.out.println("注册 SQL 执行影响行数: " + affectedRows);
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    // 用户登录
    public User login(String account, String password) throws SQLException {
        // 修改SQL，增加 user_id 匹配
        String sql = "SELECT * FROM users WHERE (user_id = ? OR nickname = ? OR phone = ? OR email = ?) AND password = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 设置所有参数（account 重复4次）
            stmt.setString(1, account);
            stmt.setString(2, account);
            stmt.setString(3, account);
            stmt.setString(4, account);
            stmt.setString(5, password);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return extractUserFromResultSet(rs);
                }
            }
        }
        return null;
    }

    // 检查用户ID是否存在
    public boolean isUserIdExist(String userId) throws SQLException {
        String sql = "SELECT 1 FROM users WHERE user_id = ?";
        return checkExistence(sql, userId);
    }

    // 检查昵称是否存在
    public boolean isNicknameExist(String nickname) throws SQLException {
        String sql = "SELECT 1 FROM users WHERE nickname = ?";
        return checkExistence(sql, nickname);
    }

    // 检查手机号是否存在
    public boolean isPhoneExist(String phone) throws SQLException {
        String sql = "SELECT 1 FROM users WHERE phone = ?";
        return checkExistence(sql, phone);
    }

    // 检查邮箱是否存在
    public boolean isEmailExist(String email) throws SQLException {
        String sql = "SELECT 1 FROM users WHERE email = ?";
        return checkExistence(sql, email);
    }

    // 更新用户信息
    public boolean updateUser(User user) throws SQLException {
        String sql = "UPDATE users SET nickname = ?, birth_date = ?, gender = ?, bio = ? WHERE user_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getNickname());
            stmt.setDate(2, user.getBirthDate());
            stmt.setString(3, user.getGender());
            stmt.setString(4, user.getBio());
            stmt.setString(5, user.getUserId());

            return stmt.executeUpdate() > 0;
        }
    }

    // 更新用户头像
    public boolean updateAvatar(String userId, String avatarPath) throws SQLException {
        String sql = "UPDATE users SET avatar = ? WHERE user_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, avatarPath);
            stmt.setString(2, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    // 根据ID获取用户
    public User getUserById(String userId) throws SQLException {
        String sql = "SELECT * FROM users WHERE user_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return extractUserFromResultSet(rs);
                }
            }
        }
        return null;
    }

    public int getFriendCount(String userId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM friendships " +
                "WHERE (user_id = ? OR friend_id = ?) " +
                "AND status = 'accepted'";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        return 0;
    }

    // 获取用户的未读好友请求数量
    public int getUnreadFriendRequests(String userId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM friendships " +
                "WHERE friend_id = ? " +
                "AND status = 'pending' " +
                "AND read_status = 'unread'";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        return 0;
    }

    // 获取用户的日志数量（新增方法）
    public int getUserPostCount(String userId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM posts WHERE user_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        return 0;
    }

    // 搜索用户
    public List<User> searchUsers(String keyword, String currentUserId) throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users WHERE (nickname LIKE ? OR user_id LIKE ?) AND user_id != ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + keyword + "%");
            stmt.setString(2, "%" + keyword + "%");
            stmt.setString(3, currentUserId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getString("user_id"));
                    user.setNickname(rs.getString("nickname"));
                    user.setAvatar(rs.getString("avatar"));
                    user.setGender(rs.getString("gender"));
                    user.setBirthDate(rs.getDate("birth_date"));
                    users.add(user);
                }
            }
        }
        return users;
    }

    public List<User> getRecommendedFriends(String userId, int limit) throws SQLException {
        List<User> recommendedUsers = new ArrayList<>();



        // 推荐逻辑：查找共同好友多的用户（简化版）
        String sql = "SELECT u.*, COUNT(f2.friend_id) AS mutual_count " +
                "FROM users u " +
                "JOIN friendships f1 ON u.user_id = f1.user_id " +
                "JOIN friendships f2 ON f1.friend_id = f2.friend_id " +
                "WHERE f2.user_id = ? " +
                "AND u.user_id != ? " +
                "AND u.user_id NOT IN ( " +
                "    SELECT friend_id FROM friendships WHERE user_id = ? AND status = 'accepted' " +
                "    UNION " +
                "    SELECT user_id FROM friendships WHERE friend_id = ? AND status = 'accepted' " +
                ") " +
                "GROUP BY u.user_id " +
                "ORDER BY mutual_count DESC " +
                "LIMIT ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, userId);
            stmt.setString(3, userId);
            stmt.setString(4, userId);
            stmt.setInt(5, limit);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getString("user_id"));
                    user.setNickname(rs.getString("nickname"));
                    user.setAvatar(rs.getString("avatar"));
                    user.setBio(rs.getString("bio"));
                    // 共同好友数（可选）
                    user.setMutualFriends(rs.getInt("mutual_count"));
                    recommendedUsers.add(user);
                }
            }
        }

        // 如果没有找到共同好友，则返回随机用户
        if (recommendedUsers.isEmpty()) {
            sql = "SELECT * FROM users " +
                    "WHERE user_id != ? " +
                    "AND user_id NOT IN ( " +
                    "    SELECT friend_id FROM friendships WHERE user_id = ? AND status = 'accepted' " +
                    "    UNION " +
                    "    SELECT user_id FROM friendships WHERE friend_id = ? AND status = 'accepted' " +
                    ") " +
                    "ORDER BY RAND() " +
                    "LIMIT ?";

            try (Connection conn = DBUtil.getConnection();
                 PreparedStatement stmt = conn.prepareStatement(sql)) {

                stmt.setString(1, userId);
                stmt.setString(2, userId);
                stmt.setString(3, userId);
                stmt.setInt(4, limit);

                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        User user = new User();
                        user.setUserId(rs.getString("user_id"));
                        user.setNickname(rs.getString("nickname"));
                        user.setAvatar(rs.getString("avatar"));
                        user.setBio(rs.getString("bio"));
                        recommendedUsers.add(user);
                    }
                }
            }
        }

        return recommendedUsers;
    }

    // 私有辅助方法
    private User extractUserFromResultSet(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserId(rs.getString("user_id"));
        user.setNickname(rs.getString("nickname"));
        user.setPhone(rs.getString("phone"));
        user.setEmail(rs.getString("email"));
        user.setAvatar(rs.getString("avatar"));
        user.setBirthDate(rs.getDate("birth_date"));
        user.setGender(rs.getString("gender"));
        user.setBio(rs.getString("bio"));
        return user;
    }

    private boolean checkExistence(String sql, String value) throws SQLException {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, value);

            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        }
    }
}